package com.emmmya.utils;

import com.emmmya.annotation.ExpressionMethod;
import com.emmmya.exception.ExpressionExcption;
import com.emmmya.executor.DefaultExecutor;
import com.emmmya.harinCheck.annotation.Check;
import com.emmmya.harinCheck.utils.HarinStringUtils;
import com.emmmya.harinCheck.utils.RegexUtils;
import com.emmmya.pojo.excel.ExcelAutoPack;
import com.emmmya.pojo.excel.MergedResult;
import com.emmmya.utils.exprossion.ExcelExprossionUtils;
import com.emmmya.utils.exprossion.ExprossionUtils;
import com.emmmya.utils.poidefault.ExcelDefaultUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author harin
 * @version 1.0
 * @date 2022-05-06 10:59:41
 */
public class ExcelUtils {
    //{{ }}表达式的正则字符串
    private static String pattern = "^\\{\\{(\\S*?)[^}}]*}}$";
    //可以使用的内置函数名称 key 为函数的正则表达式,value为对应的方法
    public static Map<String, Method> METHOD_NAME_MAP = new HashMap<>();
    static{
        Method[] methods = ExcelUtils.class.getMethods();

        if(methods != null && methods.length>0){
            for (Method method : methods) {
                ExpressionMethod annotation = method.getAnnotation(ExpressionMethod.class);
                if(annotation != null){
                    METHOD_NAME_MAP.put("^"+method.getName()+"\\((.+?)*\\)$",method);
                    String alias = annotation.alias();
                    if(HarinStringUtils.isNotEmpty(alias)){
                        METHOD_NAME_MAP.put("^"+alias+"\\((.+?)\\)*$",method);
                    }
                }
            }
        }
    }

    //自定义列

    public static<T> void autoPack(Sheet sheet,List<? extends ExcelAutoPack> titlePacks,List<T> dataList){
        //根据sort升序排列
        titlePacks = titlePacks.stream().sorted(Comparator.comparing(e -> e.getSort())).collect(Collectors.toList());
        Row titleRow = sheet.createRow(0);
        writeTitleContent(titleRow,titlePacks);

        int rowIndex = 1;
        Row row = null;
        Cell cell = null;

        for (T t : dataList) {
            row = sheet.createRow(rowIndex++);
            for (int i = 0; i < titlePacks.size(); i++) {
                ExcelAutoPack ap = titlePacks.get(i);
                cell = row.createCell(i);
                String titleName = ap.getTitleName();
                Object fieldValue = ExcelExprossionUtils.getFieldValueEnhanced(t, titleName, ap);
                setCellValue(cell,fieldValue);
            }
        }
        ExcelDefaultUtils.setColumnWidth(sheet);
    }



    /**
     * 写入自定义列的标题内容
     * @param titleRow      标题内容所在行
     * @param titlePacks    标题内容
     */
    public static void writeTitleContent(Row titleRow ,List<? extends ExcelAutoPack> titlePacks){
        for (int i = 0; i < titlePacks.size(); i++) {
            ExcelAutoPack ap = titlePacks.get(i);
            Cell cell = titleRow.createCell(i);
            String titleName =  HarinStringUtils.isNotEmpty(ap.getAliasTtileName())? ap.getAliasTtileName():ap.getTitleName();
            cell.setCellValue(titleName);
        }
    }







    //使用excel模板读取数据
    public static void injection(String filePath, Integer sheetIndex, Object obj, OutputStream os) throws IOException, IllegalAccessException {
        Workbook wb = injection(filePath, sheetIndex, obj);
        wb.write(os);
        os.close();
        wb.close();
    }


    public static Workbook injection(String filePath,Integer sheetIndex,Object obj) throws IllegalAccessException, IOException {
            Workbook wb = null;
            FileInputStream inputStream = new FileInputStream(filePath);
            if(filePath.endsWith("xlsx")) {
                // 生成xlsx的Excel
                wb = new XSSFWorkbook(inputStream);
            }
            else if(filePath.endsWith("xls")) {
                // 如需生成xls的Excel，请使用下面的工作簿对象，注意后续输出时文件后缀名也需更改为xls
                wb = new HSSFWorkbook(inputStream);
            }
            injection(wb,sheetIndex,obj);
            return wb;
    }

    public static void injection(Workbook wb,Integer sheetIndex,Object obj, OutputStream os) throws IllegalAccessException,IOException {
        Sheet sheet = wb.getSheetAt(sheetIndex);
        Map<String, Cell> stringCellMap = scanExcelTamplate(sheet);
        writeData(wb,sheetIndex,stringCellMap,obj);
        wb.write(os);
        os.close();
        wb.close();
    }

    private static void injection(Workbook wb,Integer sheetIndex,Object obj) throws IllegalAccessException {
        Sheet sheet = wb.getSheetAt(sheetIndex);
        injection(sheet,obj);
    }

    public static void injection(Sheet sheet,Object obj, OutputStream os) throws IllegalAccessException,IOException {
        injection(sheet,obj);
        Workbook wb = sheet.getWorkbook();
        wb.write(os);
        os.close();
        wb.close();
    }

    private static void injection(Sheet sheet,Object obj) throws IllegalAccessException {
        Map<String, Cell> stringCellMap = scanExcelTamplate(sheet);
        if(Map.class.isInstance(obj)){
            writeData(sheet, stringCellMap, (Map)obj);
        }else {
            writeData(sheet, stringCellMap, obj);
        }
//        writeData(sheet,stringCellMap,obj);
    }


    /**
     * 核心方法，扫描excel需要操作的位置。
     * 扫描excel的内容，判断每一个单元格是否存在{{表达式}}
     * @param wb
     * @return
     */
    public static Map<String,Cell> scanExcelTamplate(Workbook wb,Integer SheetIndex){
        Sheet sheet = wb.getSheetAt(SheetIndex);
        return scanExcelTamplate(sheet);
    }

    public static Map<String,Cell> scanExcelTamplate(Sheet sheet){
        int lastRowNum = sheet.getLastRowNum();
//        HashMap<String, Point> cellFieldNameMap = new HashMap<>();
        //使用LinkedHashMap保证插入的顺序，逐行逐个单元格解析。 for讯号
        LinkedHashMap<String, Cell> cellFieldNameMap = new LinkedHashMap<>();
        o:for (int rowNum = 0; rowNum <= lastRowNum; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if(row == null){
                continue o;
            }
            int lastCellNum = row.getLastCellNum();

            a:for (int cellNum = 0; cellNum < lastCellNum; cellNum++) {
                Cell cell = row.getCell(cellNum);
                if(cell == null) {continue a;}
                if(cell.getCellType().getCode() != CellType.STRING.getCode()) {continue a;}

                String cellValue = cell.getStringCellValue().trim();

                if(RegexUtils.findAny(pattern,cellValue)){
                    //Point point = new Point(rowNum, cellNum);
                    String available = ExprossionUtils.removeCurlyBracket(cellValue);
                    cellFieldNameMap.put(available,cell);
                }

            }
        }
        return cellFieldNameMap;
    }


    /**
     * ==========================
     */
    public static void writeData(Workbook wb,Integer sheetNo, Map<String, Cell> cellFieldNameMap,Object obj) throws IllegalAccessException {
        if(wb != null){
            Sheet sheet = wb.getSheetAt(sheetNo);
            if(Map.class.isInstance(obj)){
                writeData(sheet, cellFieldNameMap, (Map)obj);
            }else {
                writeData(sheet, cellFieldNameMap, obj);
            }
        }
    }

    /**
     * 优先级：
     *  ①优先处理 {{属性}}，单个属性的赋值
     *  ②再处理函数与链式属性的调用，如：for(用户 in 用户列表) 、{{用户.角色.角色名}}，但函数依然比链式属性优先级高一丢丢
     * @param sheet
     * @param cellFieldNameMap
     * @param obj
     * @throws IllegalAccessException
     */
    private static void writeData(Sheet sheet,Map<String, Cell> cellFieldNameMap,Object obj) throws IllegalAccessException {
        long start = System.currentTimeMillis();
        Field[] fields = ExprossionUtils.getAllFields(obj);
        long last = System.currentTimeMillis();
        System.out.println("获取所有的属性，花时："+(last - start) +"毫秒");
        start = System.currentTimeMillis();
        //优先级①
        for (Field field : fields) {
            field.setAccessible(true);
            Check check = field.getAnnotation(Check.class);
            if(check!= null){
                String name = check.name();
                if(cellFieldNameMap.containsKey(name)){
                    //获取sheet下的指定name的单元格
                    Cell cell = getCell(sheet, cellFieldNameMap, name);
                    if(cell == null) continue;
                    Object data = field.get(obj);
                    if(data == null) continue;
                    cell.setCellValue(data.toString());
                    cellFieldNameMap.remove(name); //去掉已经替换的单元格
                }
            }
        }
        last = System.currentTimeMillis();
        System.out.println("优先级① 花时："+(last - start) +"毫秒");

        start = System.currentTimeMillis();
        //优先级②
        Set<String> keySet = cellFieldNameMap.keySet();
        ArrayList<Object> params = new ArrayList<>();
        params.add(obj);
        params.add(obj);
        for (String name : keySet) {
            Cell cell = getCell(sheet, cellFieldNameMap, name);
            if (cell == null) continue;
            if(cell.getCellType().getCode() != CellType.STRING.getCode()) {continue;}
            String cellValue = cell.getStringCellValue();
            if(HarinStringUtils.isEmpty(cellValue))continue;
            //如果记录的表达式与当前的单元格表达式不相同，则不处理
            String exprossion = ExprossionUtils.removeCurlyBracket(cellValue);
            if(!exprossion.equals(name)) continue;
            params.set(1,cell);
            Method formulaMethod = ExprossionUtils.matchFunction(cellValue, METHOD_NAME_MAP,params);
            //函数执行方式
            if(formulaMethod != null) {
                functionExecute(formulaMethod,params);
            }
            //链式属性 或 过滤器 调用方式
            else {
                Object value = ExprossionUtils.getFieldValueByChain(obj, name, METHOD_NAME_MAP);
                if(value != null){
                    cell.setCellValue(value.toString());
                }
            }
        }
        last = System.currentTimeMillis();
        System.out.println("优先级② 花时："+(last - start) +"毫秒");
        Workbook workbook = sheet.getWorkbook();
        workbook.setForceFormulaRecalculation(true);
    }

    /**
     * map类型的数据类型解析
     * @param sheet
     * @param cellFieldNameMap
     * @param dataMap
     */
    private static void writeData(Sheet sheet,Map<String, Cell> cellFieldNameMap,Map dataMap){
        if(cellFieldNameMap != null && dataMap!= null && dataMap.size() > 0){
            Set<String> keySet = cellFieldNameMap.keySet();
            ArrayList<Object> params = new ArrayList<>();
            params.add(cellFieldNameMap);
            params.add(cellFieldNameMap);
            for (String fieldName : keySet) {
                Cell cell = getCell(sheet, cellFieldNameMap, fieldName);
                if(cell == null) continue;
                if(cell.getCellType().getCode() != CellType.STRING.getCode()) {continue;}
                //直接根据excel中的表达式内容匹配  {{内容}}
                if(dataMap.containsKey(fieldName)){
                    Object data = dataMap.get(fieldName);
                    if(data == null) continue;
                    cell.setCellValue(data.toString());
                }else{ //不能直接匹配，则判断是否为内置函数或
                    params.set(1,cell);

                    String cellValue = cell.getStringCellValue();
                    String otherStr = ExprossionUtils.removeCurlyBracket(cellValue);
                    Method functionMethod = ExprossionUtils.matchFunction(otherStr, METHOD_NAME_MAP,params);
                    //函数执行
                    if(functionMethod != null){
                        functionExecute(functionMethod,params);
                    }else{//链式编程调用
                        Object value = ExprossionUtils.getFieldValueByChain(dataMap, otherStr, METHOD_NAME_MAP);
                        if(value != null){
                            cell.setCellValue(value.toString());
                        }
                    }
                }
            }
        }
    }

    /**
     * 在sheet页下，获取name的cellFieldNameMap存在的单元格
     * @param sheet             sheet页面
     * @param cellFieldNameMap  单元格存储位置 x,y
     * @param name              哪个数据量
     * @return
     */

    private static Cell getCell(Sheet sheet, Map<String, Cell> cellFieldNameMap, String name) {
        try {
            Cell cell = cellFieldNameMap.get(name);
            CellType cellType = cell.getCellType();
            return cell;
        } catch (Exception e) {
            //e.printStackTrace();
        }
        return null;
    }


    /**
     * 函数调用，
     * @param method            待执行的方法
     * @param params            参数集合 index:1 执行方法传入的第一个参数 index:2 代操作的单元格
     */
    public static Object functionExecute(Method method,List<Object> params){
        ExpressionMethod annotation = method.getAnnotation(ExpressionMethod.class);
        String before = annotation.before();
        try {
            if(HarinStringUtils.isNotEmpty(before)){
                Method beforMethod = ExprossionUtils.matchFunction(before, METHOD_NAME_MAP,params);
                if(beforMethod!= null){
                   return DefaultExecutor.executeMethod(beforMethod, params.toArray());
                }
            }else {
                return DefaultExecutor.executeMethod(method, params.toArray());
            }
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return null;
    }


    //excel内置函数


    //excel 公式处理

    @ExpressionMethod(alias="=normal")
    public static void normal(Object obj,Cell cell){
        String cellValue = cell.getStringCellValue();
        if(!HarinStringUtils.isEmpty(cellValue)) {
            String s = ExprossionUtils.removeCurlyBracket(cellValue);

        }
    }

    //excel SUM公式处理
    @ExpressionMethod(alias="=SUM",before = "=SUM()")
    public static void excelSum(Object obj,Cell cell)  throws Exception{
        String cellValue = cell.getStringCellValue();
        if(!HarinStringUtils.isEmpty(cellValue)) {
            //移除两边花括号
            String newFotlate = ExprossionUtils.removeCurlyBracket(cellValue).trim();
            //获取参数
            int sumIndex = newFotlate.indexOf("=SUM(");
            String substring = newFotlate.substring(sumIndex+5, newFotlate.length() - 1);
            List<Object> objects = ExcelExprossionUtils.parseExcelFunctionParam(substring, ":");
            //解析excel 自身的函数
            StringBuilder sum = new StringBuilder("SUM(");
            for (int i = 0; i < objects.size(); i++) {
//                sum.append(objects.get(i).toString()+":");
                Object fieldValue = ExcelExprossionUtils.parseExcelFunction(cell,objects.get(i),METHOD_NAME_MAP);
                if(HarinStringUtils.isNotEmpty(fieldValue)) {
                    sum.append(fieldValue.toString() + ":");
                }
            }
            String sumResult = sum.substring(0, sum.length() - 1);

            sumResult = sumResult + ")";
            //cell.setCellType(CellType.FORMULA);
            cell.setCellValue("");
            cell.setCellFormula(sumResult);
        }
    }


    /**
     * 单元格上下左右移动方法
     */
    @ExpressionMethod(alias="upper")
    public static Cell excelCellUpper(Cell cell,Integer offset)  throws Exception{
        Cell targetCell = null;
        if(cell == null)return cell;
        Sheet sheet = cell.getSheet();
        Row row = sheet.getRow(cell.getRowIndex() - offset);
        if(row == null){
            row = sheet.createRow(cell.getRowIndex() - offset);
            targetCell = row.createCell(cell.getColumnIndex());
        }else {
            targetCell = row.getCell(cell.getColumnIndex());
        }

        return targetCell;
    }

    /**
     * 下方单元格
     * @param cell
     * @param offset
     * @return
     */
    @ExpressionMethod(alias="below")
    public static Cell excelCellBelow(Cell cell,Integer offset)  throws Exception{
        Cell targetCell = null;
        Sheet sheet = cell.getSheet();
        Row row = sheet.getRow(cell.getRowIndex() + offset);
        if(row == null){
            row = sheet.createRow(cell.getRowIndex() + offset);
            targetCell = row.createCell(cell.getColumnIndex());
        }else {
            targetCell = row.getCell(cell.getColumnIndex());
        }
        return targetCell;
    }

    @ExpressionMethod(alias="left")
    public static Cell excelCellLeft(Cell cell,Integer offset)  throws Exception{
        Row row = cell.getRow();
        Cell targetCell = row.getCell(cell.getColumnIndex() - offset);
        return targetCell;
    }

    @ExpressionMethod(alias="right")
    public static Cell excelCellRight(Cell cell,Integer offset)  throws Exception{
        Row row = cell.getRow();
        Cell targetCell = row.getCell(cell.getColumnIndex() + offset);
        return targetCell;
    }

    @ExpressionMethod(alias="getCellCode")
    public static String getCellCode(Cell cell) throws Exception{
        if(cell == null) return "";
        String s = ExcelDefaultUtils.excelColIndexToStr(cell.getColumnIndex()+1);
        return s+(cell.getRowIndex()+1);
    }


    @ExpressionMethod(alias="forEach",after = true)
    public static<T> Integer forEach(Object source,Cell cell,String sourceFieldName,String feildName,Boolean startFlag) throws Exception{
        if(cell == null){
            return 0;
        }
        int result = 0;
        try {
            String cellValue = cell.getStringCellValue();
            cellValue = ExprossionUtils.removeCurlyBracket(cellValue);
            List<String> functionList = Arrays.stream(cellValue.split("\\|")).collect(Collectors.toList());
            functionList.remove(0);
            functionList.remove(0);

            Object fieldValue = ExprossionUtils.getFieldValue(source, sourceFieldName);
            Collection<Object> dataList = (Collection<Object>)fieldValue;
            List<Object> collect = dataList.stream().collect(Collectors.toList());
            if(collect == null || collect.size() == 0){
                return 0;
            }
            Row row = cell.getRow();
            int rowNum = row.getRowNum();
            int columnIndex = cell.getColumnIndex();
            Sheet sheet = row.getSheet();
            if(startFlag && collect.size() > 1) {
                ExcelDefaultUtils.insertAndCreateRow(sheet,rowNum+1,dataList.size()-1,row);
                result = collect.size()-1;
                //把当前行的前面的单元格合并
                for(int z = columnIndex-1; z >=0;z--){
                    ExcelDefaultUtils.mergedRegion(sheet,rowNum,rowNum+dataList.size()-1,z,z);

                }
            }
            Object o = collect.get(0);
            Object fieldValue1 = ExprossionUtils.getFieldValue(o, feildName);
            List<Object> params = new ArrayList<>();
            if(!functionList.isEmpty() ){
                for (String funName : functionList) {
                    params.add(fieldValue1);
                    params.add(cell);
                    List<Object> parseParams = ExprossionUtils.getFilterFunParams(null, fieldValue1, funName, ",");
                    params.addAll(parseParams);
                    Method formulaMethod = ExcelExprossionUtils.matchingFunction(METHOD_NAME_MAP,funName, params);
                    if(formulaMethod!= null){
                        fieldValue1 = functionExecute(formulaMethod, params);
                    }else{
                        params.remove(cell);
                        formulaMethod = ExcelExprossionUtils.matchingFunction(METHOD_NAME_MAP,funName, params);
                        if(formulaMethod != null){
                            fieldValue1 = functionExecute(formulaMethod, params);
                        }
                    }
                }
            }

            setCellValue(cell,HarinStringUtils.isEmpty(fieldValue1)?"":fieldValue1);
            for (int i = 1; i < collect.size(); i++) {
                o = collect.get(i);
                Row row1 = sheet.getRow(rowNum + i);
                if(row1 == null){
                    row1 = sheet.createRow(rowNum + i);
                }
                cell = row1.getCell(columnIndex);
                if(cell == null){
                    cell = row1.createCell(columnIndex);
                }
                fieldValue1 = ExprossionUtils.getFieldValue(o, feildName);
                if(!functionList.isEmpty() ){
                    for (String funName : functionList) {
                        params.clear();
                        params.add(fieldValue1);
                        params.add(cell);
                        List<Object> parseParams = ExprossionUtils.getFilterFunParams(null, fieldValue1, funName, ",");
                        params.addAll(parseParams);
                        Method formulaMethod = ExcelExprossionUtils.matchingFunction(METHOD_NAME_MAP,funName, params);
                        if(formulaMethod!= null){
                            fieldValue1 = functionExecute(formulaMethod, params);
                        }else{
                            params.remove(cell);
                            formulaMethod = ExcelExprossionUtils.matchingFunction(METHOD_NAME_MAP,funName, params);
                            if(formulaMethod != null){
                                fieldValue1 = functionExecute(formulaMethod, params);
                            }
                        }
                    }
                }
                setCellValue(cell,HarinStringUtils.isEmpty(fieldValue1)?"":fieldValue1);
            }
        } catch (Exception e) {
            e.printStackTrace();
            setCellValue(cell,"");
            result = 0;
        }
        return result;

    }




    /**
     * for循环函数执行前的方法,解析for内部结构
     * @param obj               待处理对象
     * @param cell              当前需要处理的单元格
     */
    @ExpressionMethod(alias="for_before")
    public static void  excelForFunctionBefore(Object obj,Cell cell) throws InvocationTargetException, IllegalAccessException {
        String cellValue = cell.getStringCellValue();
        if(!HarinStringUtils.isEmpty(cellValue)) {
            String newFotlate = ExprossionUtils.removeCurlyBracket(cellValue);
            String substring = newFotlate.substring(4, newFotlate.length() - 1);
            //拆解for循环内部的结构   item  in  list
            Pattern compile = Pattern.compile(ExprossionUtils.FOR_FUNCTION_PARAM);
            boolean any = RegexUtils.findAny(compile, substring);
            if(!any){
                throw new ExpressionExcption("解析for函数出错了！");
            }
            String[] ins = substring.split(ExprossionUtils.FOR_FUNCTION_PARAM);
            Object fieldValue = ExprossionUtils.getExprossionValue(obj,ins[1].trim());
            Row startRow = cell.getRow();
            Sheet sheet = startRow.getSheet();
            int rowIndex = cell.getRowIndex();
            Row row = null;

            //判断该位置是否为合并单元格
            MergedResult mergedRegion = ExcelDefaultUtils.isMergedRegion(sheet, rowIndex, cell.getColumnIndex());
            if(mergedRegion.getFlag()){
                //如果为合并单元格，则获取合并单元格的下一行
                Integer lastRow = mergedRegion.getLastRow();
                row = sheet.getRow(lastRow + 1);
                //移除掉当前的{{for}}所在行
                ExcelDefaultUtils.removeRow(sheet,mergedRegion.getLastRow(),mergedRegion.getLastRow()-mergedRegion.getFirstRow()+1);
            }else {
                //如果不是合并单元格，则直接获取当前行下一行
                row = sheet.getRow(cell.getRow().getRowNum() + 1);
                //移除掉当前的{{for}}所在行
                ExcelDefaultUtils.removeRow(sheet,rowIndex);
            }
            short lastCellNum = row.getLastCellNum();
            List<Cell> cellList = new ArrayList<>();
            for (int i = 0; i < lastCellNum; i++) {
                cellList.add(row.getCell(i));
            }
            //DefaultExecutor.executeMethod(method, new Object[]{fieldValue, ins[0].trim(), cellList});
            excelForFunction((List)fieldValue, ins[0].trim(), cellList);
        }
    }


    /**
     * for循环函数          内置函数
     * @param source       需要循环的数据
     * @param itemRef
     * @param cellList
     * @param <T>
     */
    @ExpressionMethod(alias = "for",before = "for_before()")
    public static <T> void excelForFunction(Collection<T> source,String itemRef,List<Cell> cellList){
        long l = System.currentTimeMillis();
        if(source == null || source.size() == 0) {
            Cell cell = cellList.get(0);
            Row row = cell.getRow();
            Sheet sheet = row.getSheet();
            ExcelDefaultUtils.removeRow(sheet,row.getRowNum());
            return;
        };
        int sourceSize = source.size();
        List<Integer> cellNums = new ArrayList<>();
        List<String> cellVlaues = new ArrayList<>();
        List<CellStyle> cellStyles = new ArrayList<>();

        for (Cell cell : cellList) {
            if(cell == null){
                cellNums.add(null);
                cellVlaues.add(null);
                cellStyles.add(null);
            }else {
                cellNums.add(cell.getColumnIndex());
                cellVlaues.add(cell.getStringCellValue());
                cellStyles.add(cell.getCellStyle());
            }
        }
        Cell headCell = cellList.get(0);
        Row row = headCell.getRow();
        Integer rowNum = row.getRowNum();
        Integer tempRowNum = rowNum;
        Sheet sheet = row.getSheet();
        CellStyle rowStyle = row.getRowStyle();
        short height = row.getHeight();

        Row newRow = null;
        Cell newCell = null;
        String cellValue = null;
        String exprossionValue = null;
        String fieldName = null;
        Object fieldValue = null;

        int cellSize = cellVlaues.size();
        //一次性插入足够多的行，第三个参数传入不创建行，那么下方九需要自己创建行
//        ExcelDefaultUtils.insertRow(sheet, rowNum, sourceSize,true);
//        //移除变量行
//        ExcelDefaultUtils.removeRow(sheet,rowNum+sourceSize-1);
        if(sourceSize> 1) {
            ExcelDefaultUtils.insertAndCreateRow(sheet, rowNum, sourceSize - 1, row);
        }
        List<Object> params = new ArrayList<>();
        int dataIndex = 1;
        Integer affectedRow = null;
        for (T e : source) {
            //自己创建行，如果上面第三个参数为true的话，就可以直接使用sheet.getRow(rowNum++);
            newRow = sheet.getRow(rowNum++);
//            newRow = sheet.createRow(rowNum++);
            newRow.setRowStyle(rowStyle);
            newRow.setHeight(height);
            //受影响的行号
            affectedRow = 0;
            for (int i=0; i< cellSize;i++) {
                cellValue = cellVlaues.get(i);
                if(cellValue == null) continue;
                exprossionValue = ExprossionUtils.removeCurlyBracket(cellValue);
                newCell = newRow.createCell(cellNums.get(i));
                newCell.setCellStyle(cellStyles.get(i));

                if(exprossionValue.equals("序号") || exprossionValue.equalsIgnoreCase("NO")){
                    setCellValue(newCell,dataIndex);
                }
                else if(exprossionValue.startsWith(itemRef+".")){
                    fieldName = exprossionValue.substring(itemRef.length() + 1);
                    if(fieldName.contains("|")){
                        affectedRow = forFilter(fieldName, cellVlaues, newCell, cellValue, exprossionValue, params, e, i);
                        //newRow = sheet.getRow(rowNum-1);
                        rowNum += affectedRow;
                    }
                    else {
                        fieldValue = ExcelExprossionUtils.getExprossionValue(e, newCell, fieldName, METHOD_NAME_MAP);
                        setCellValue(newCell,fieldValue);
                    }
                }
                else{ // 非item中的数据时，可能是需要执行的函数
                    params.clear();
                    params.add(e);
                    params.add(newCell);
                    Method formulaMethod = ExprossionUtils.matchFunction(cellValue, METHOD_NAME_MAP,params);
                    //函数执行方式
                    if(formulaMethod != null) {
                        newCell.setCellValue(cellVlaues.get(i));
//                        functionExecute(formulaMethod,e,newCell);
                        functionExecute(formulaMethod,params);
                    }else{
                        //注意，for循环中的数据，一定要把单元格传入进去
                        fieldValue = ExcelExprossionUtils.getExprossionValue(e,newCell, exprossionValue, METHOD_NAME_MAP);
                        if(fieldValue != null){
                            setCellValue(newCell,fieldValue);
                        }
                    }
                }
            }
            rowNum += affectedRow;
            dataIndex++;
        }

        long l1 = System.currentTimeMillis();
        System.out.println("for耗时："+(l1 - l)+"毫秒");
    }

    /**
     * for循环中，使用过滤器函数时的处理方法
     * @param fieldName         待处理的属性名称
     * @param cellVlaues        参与便利的单元格的值
     * @param newCell           当前单元格
     * @param cellValue
     * @param exprossionValue
     * @param params
     * @param e
     * @param i
     * @param <T>
     * @return
     */
    private static <T> Integer forFilter(String fieldName,List<String> cellVlaues, Cell newCell, String cellValue, String exprossionValue, List<Object> params, T e, int i) {
        List<String> collect = Arrays.stream(fieldName.split("\\|")).collect(Collectors.toList());
        Integer affectedRow = 0;
        Object fieldValue = null;
        String newFieldName = collect.get(0).trim();
        //Object newFieldValue = ExcelExprossionUtils.getExprossionValue(e, newFieldName);
        for (int i1 = 1; i1 < collect.size(); i1++) {
            String functionName = collect.get(i1).trim();
            params.clear();
            params.add(e);
            params.add(newCell);
            params.add(newFieldName);
            List<Object> parseParams = ExprossionUtils.getFilterFunParams(null, e, collect.get(i1).trim(), ",");
            params.addAll(parseParams);
            Method formulaMethod = ExcelExprossionUtils.matchingFunction(METHOD_NAME_MAP,functionName, params);
//            ExpressionMethod expressionMethod = formulaMethod.getAnnotation(ExpressionMethod.class);
            if (formulaMethod != null) {
                newCell.setCellValue(cellVlaues.get(i));
                Object o = functionExecute(formulaMethod, params);
                affectedRow = Integer.valueOf(o.toString());
                //阻止之后的函数继续执行，再受影响行存在时候，就不允许该防暑继续执行了
                return affectedRow;
            }

            //执行普通的函数，返回值为函数执行结果
            params.clear();
            if(i1 == 1) {
                params.add(ExcelExprossionUtils.getExprossionValue(e, newFieldName));
            }else {
                params.add(fieldValue);
            }
            params.add(newCell);
            parseParams = ExcelExprossionUtils.getFilterFunParams(null, e, collect.get(i1).trim(), ",");
            params.addAll(parseParams);
            formulaMethod = ExcelExprossionUtils.matchingFunction(METHOD_NAME_MAP,functionName, params);
            if(formulaMethod!= null){
                newCell.setCellValue(cellVlaues.get(i));
                fieldValue = functionExecute(formulaMethod, params);
            }else {
                params.remove(newCell);
                formulaMethod = ExcelExprossionUtils.matchingFunction(METHOD_NAME_MAP,functionName, params);
                if(formulaMethod != null){
                    newCell.setCellValue(cellVlaues.get(i));
                    fieldValue = functionExecute(formulaMethod, params);
                }else {
                    fieldValue = ExcelExprossionUtils.getExprossionValue(e, newCell, exprossionValue, METHOD_NAME_MAP);
                }
            }
        }
        if (fieldValue != null) {
            setCellValue(newCell, fieldValue);
        }else{
            setCellValue(newCell, "");
        }
        return 0;
    }


    /**
     * 过滤器函数
     * @param data          需要转成数字的数据
     * @param cell          对应需要操作的单元格
     * @param retain        保留几位小数位     默认为0
     * @return
     */
    @ExpressionMethod(alias = "toNumber")
    public static Object toNumber(Object data,Cell cell,Integer retain)  throws Exception{
        if(data == null){
            return "";
        }
        if(retain == null){retain = 0;}
        BigDecimal res = BigDecimal.ZERO;
        if(data instanceof String){
            res = res.add(new BigDecimal(data.toString()));
        }
        if(retain > 0) {
            CellStyle cellStyle = cell.getCellStyle();
            String format = String.format("0.%0" + retain + "d", 0);
            Workbook workbook = cell.getSheet().getWorkbook();
            DataFormat dataFormat = workbook.createDataFormat();
            short builtinFormat = dataFormat.getFormat(format);
            cellStyle.setDataFormat(builtinFormat);
            cell.setCellStyle(cellStyle);
        }
        cell.setCellValue(0D);  //先给个默认值，不然上面格式设置不会生效。
        return res.setScale(retain,BigDecimal.ROUND_HALF_UP).doubleValue();    //阻止后续还继续设置单元格内容
    }

    /**
     * 给单元格设置值
     * @param cell      需要设置值的单元格
     * @param data      数据
     */
    public static void setCellValue(Cell cell,Object data){
       if( cell!= null && data != null){
           //int cellType = cell.getCellType().getCode();
           if(data instanceof Number){
               cell.setCellValue(new BigDecimal(data.toString()).doubleValue());
           }else{
               cell.setCellValue(data.toString());
           }
       }
    }


}
